"""
用户管理API路由
"""
from typing import Any, List, Optional
from fastapi import APIRouter, Depends, HTTPException, status, Query, UploadFile, File, Body
from fastapi.responses import StreamingResponse
import io
from motor.motor_asyncio import AsyncIOMotorDatabase
from bson import ObjectId
from pymongo import DESCENDING
from pydantic import BaseModel, Field
# import pandas as pd  # 暂时注释掉pandas导入
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

from app.api.deps import get_db, get_current_user
from app.models.user import (
    UserCreate, 
    UserUpdate, 
    UserStatus,
    UserRole
)
from app.core.permissions import Permissions, get_permissions_for_role
from app.core.response import success_response, error_response, ApiResponse
from app.core.security import get_password_hash
from app.services.user_service import UserService

router = APIRouter()

# 统计接口需要放在 /{user_id} 之前，避免路由冲突
@router.get("/stats", response_model=ApiResponse, summary="获取用户统计信息")
async def get_user_stats(
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    获取用户统计信息
    需要权限：user:read 或 admin 角色
    """
    import logging
    logger = logging.getLogger(__name__)
    
    try:
        # 检查权限
        user_permissions = current_user.get("permissions", [])
        user_roles = current_user.get("roles", [])
        
        # 管理员拥有所有权限，或者拥有通配符权限
        has_permission = (
            "admin" in user_roles or 
            "*" in user_permissions or 
            Permissions.USER_READ in user_permissions
        )
        
        if not has_permission:
            logger.warning(f"User {current_user.get('user_id')} attempted to access user stats without permission")
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="Not enough permissions to access user statistics"
            )
        
        # 使用服务层获取统计数据
        stats_data = await UserService.get_user_stats(db)
        
        logger.info(f"User stats retrieved successfully for user {current_user.get('user_id')}: {stats_data}")
        return success_response(data=stats_data)
        
    except HTTPException:
        # 重新抛出 HTTPException
        raise
    except Exception as e:
        logger.error(f"Error retrieving user stats: {str(e)}")
        return error_response(
            error_type="internal_error",
            message="Internal server error while retrieving user statistics",
            code=status.HTTP_500_INTERNAL_SERVER_ERROR
        )

@router.get("/departments/list", response_model=ApiResponse, summary="获取部门列表")
async def get_departments(
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    获取所有部门列表
    """
    departments = await UserService.get_departments(db)
    return success_response(data={"departments": departments})

@router.get("/roles/list", response_model=ApiResponse, summary="获取角色列表")
async def get_roles(
    current_user: dict = Depends(get_current_user)
) -> Any:
    """
    获取所有角色列表
    """
    roles = UserService.get_roles()
    return success_response(data={"roles": roles})

@router.get("/permissions/list", response_model=ApiResponse, summary="获取权限列表")
async def get_permissions(
    current_user: dict = Depends(get_current_user)
) -> Any:
    """
    获取所有权限列表
    """
    permissions = [
        perm for perm in dir(Permissions) 
        if not perm.startswith('_') and isinstance(getattr(Permissions, perm), str)
    ]
    return success_response(data={"permissions": permissions})

@router.get("/search", response_model=ApiResponse, summary="搜索用户用于项目添加")
async def search_users_for_project(
    search: Optional[str] = Query(None, description="搜索关键词 (用户名或邮箱)"),
    project_id: Optional[str] = Query(None, description="项目ID，用于排除已存在的成员"),
    limit: int = Query(20, ge=1, le=200, description="返回的记录数"),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    搜索用户（通常用于添加到项目）
    - 可根据用户名或邮箱进行模糊搜索
    - 如果提供了 project_id，会排除该项目中已存在的成员
    """
    # 使用服务层获取用户数据
    result = await UserService.search_users_for_project(db, search, project_id, limit)
    
    return success_response(data=result)

@router.get("", response_model=None, summary="获取用户列表（分页）")
async def get_users(
    skip: int = Query(0, ge=0, description="跳过的记录数"),
    limit: int = Query(20, ge=1, le=200, description="返回的记录数"),
    search: Optional[str] = Query(None, description="搜索关键词"),
    status: Optional[UserStatus] = Query(None, description="用户状态筛选"),
    role: Optional[str] = Query(None, description="用户角色筛选"),
    department: Optional[str] = Query(None, description="部门筛选"),
    sort_by: str = Query("created_at", description="排序字段"),
    sort_order: str = Query("desc", regex="^(asc|desc)$", description="排序方向"),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    获取用户列表
    需要权限：user:read
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_READ, Permissions.ADMIN]):
        return error_response(
            error_type="permission_denied",
            message="Not enough permissions",
            code=status.HTTP_403_FORBIDDEN
        )
    
    # 使用服务层获取用户数据
    items, total = await UserService.get_users(
        db,
        search=search,
        status=status,
        role=role,
        department=department,
        skip=skip,
        limit=limit,
        sort_by=sort_by,
        sort_order=1 if sort_order == "asc" else -1
    )
    
    # 计算分页信息
    total_pages = (total + limit - 1) // limit  # 向上取整
    current_page = (skip // limit) + 1
    
    # 返回分页格式的数据
    result = {
        "items": items,
        "pagination": {
            "total": total,
            "page": current_page,
            "size": limit,
            "pages": total_pages,
            "has_next": current_page < total_pages,
            "has_prev": current_page > 1
        }
    }
    
    return success_response(data=result)

@router.get("/count", response_model=ApiResponse, summary="获取用户总数")
async def get_users_count(
    search: Optional[str] = Query(None, description="搜索关键词"),
    status: Optional[UserStatus] = Query(None, description="用户状态筛选"),
    role: Optional[str] = Query(None, description="用户角色筛选"),
    department: Optional[str] = Query(None, description="部门筛选"),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    获取用户总数
    需要权限：user:read
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_READ, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 使用服务层获取用户数量
    total = await UserService.get_users_count(
        db,
        search=search,
        status=status,
        role=role,
        department=department
    )
    
    return success_response(data={"total": total})







@router.get("/{user_id}", response_model=ApiResponse, summary="获取用户详情")
async def get_user(
    user_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    获取用户详情
    需要权限：user:read 或者是用户本人
    """
    # 检查是否是用户本人或有权限
    is_self = current_user.get("user_id") == user_id
    user_permissions = current_user.get("permissions", [])
    user_roles = current_user.get("roles", [])
    has_permission = ("admin" in user_roles or "*" in user_permissions or Permissions.USER_READ in user_permissions)
    
    if not is_self and not has_permission:
        return error_response(
            error_type="permission_denied",
            message="Not enough permissions",
            code=status.HTTP_403_FORBIDDEN
        )
    
    # 使用服务层获取用户详情
    user_response = await UserService.get_user(db, user_id)
    
    if not user_response:
        return error_response(
            error_type="not_found",
            message="User not found",
            code=status.HTTP_404_NOT_FOUND
        )
    return success_response(data=user_response)

@router.post("", response_model=ApiResponse, summary="创建用户")
async def create_user(
    user_in: UserCreate,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    创建用户
    需要权限：user:create
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_CREATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 使用服务层创建用户
    user_response = await UserService.create_user(db, user_in)
    
    if not user_response:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Failed to create user"
        )
    return success_response(data=user_response)

@router.put("/{user_id}", response_model=ApiResponse, summary="更新用户信息")
async def update_user(
    user_id: str,
    user_in: UserUpdate,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    更新用户信息
    需要权限：user:update 或者是用户本人（但只能更新自己的部分信息）
    """
    # 检查权限
    is_self = current_user.get("user_id") == user_id
    user_permissions = current_user.get("permissions", [])
    user_roles = current_user.get("roles", [])
    has_permission = ("admin" in user_roles or "*" in user_permissions or Permissions.USER_UPDATE in user_permissions)
    
    if not is_self and not has_permission:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 使用服务层更新用户
    user_response = await UserService.update_user(db, user_id, user_in, is_self)
    
    if not user_response:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Failed to update user"
        )
    
    return success_response(data=user_response)

@router.delete("/{user_id}", response_model=ApiResponse, summary="删除用户（软删除）")
async def delete_user(
    user_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    删除用户
    需要权限：user:delete
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_DELETE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 验证ObjectId格式
    if not ObjectId.is_valid(user_id):
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Invalid user ID format"
        )
    object_id = ObjectId(user_id)
    
    # 检查用户是否存在
    user = await db.users.find_one({"_id": object_id})
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # 不能删除自己
    if current_user.get("user_id") == user_id:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Cannot delete yourself"
        )
    
    # 软删除：更新状态为已删除
    await db.users.update_one(
        {"_id": object_id},
        {"$set": {"status": UserStatus.DELETED}}
    )
    
    return success_response(message="User deleted successfully")

@router.post("/{user_id}/activate", response_model=ApiResponse, summary="激活用户")
async def activate_user(
    user_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    激活用户
    需要权限：user:update
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_UPDATE, Permissions.ADMIN]):
        return error_response(
            error_type="permission_denied",
            message="Not enough permissions",
            code=status.HTTP_403_FORBIDDEN
        )
    
    # 验证ObjectId格式
    if not ObjectId.is_valid(user_id):
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Invalid user ID format"
        )
    object_id = ObjectId(user_id)
    
    # 检查用户是否存在
    user = await db.users.find_one({"_id": object_id})
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # 激活用户
    await db.users.update_one(
        {"_id": object_id},
        {"$set": {"status": UserStatus.ACTIVE}}
    )
    
    return success_response(message="User activated successfully")

@router.post("/{user_id}/deactivate", response_model=ApiResponse, summary="停用用户")
async def deactivate_user(
    user_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    停用用户
    需要权限：user:update
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_UPDATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 验证ObjectId格式
    if not ObjectId.is_valid(user_id):
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Invalid user ID format"
        )
    object_id = ObjectId(user_id)
    
    # 检查用户是否存在
    user = await db.users.find_one({"_id": object_id})
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # 不能停用自己
    if current_user.get("user_id") == user_id:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Cannot deactivate yourself"
        )
    
    # 停用用户
    await db.users.update_one(
        {"_id": object_id},
        {"$set": {"status": UserStatus.INACTIVE}}
    )
    
    return success_response(message="User deactivated successfully")





# 删除重复的路由定义，保留第119行的版本


@router.post("/{user_id}/reset-password", response_model=ApiResponse, summary="重置用户密码")
async def reset_user_password(
    user_id: str,
    password_data: dict = Body(...),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    重置用户密码
    需要权限：user:update
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_UPDATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 验证ObjectId格式
    if not ObjectId.is_valid(user_id):
        return error_response(
            error_type="VALIDATION_ERROR",
            message=f"Invalid user ID format: {user_id}",
            code=status.HTTP_400_BAD_REQUEST
        )
    
    object_id = ObjectId(user_id)
    
    # 检查用户是否存在
    user = await db.users.find_one({"_id": object_id})
    if not user:
        return error_response(
            error_type="NOT_FOUND_ERROR",
            message="User not found",
            code=status.HTTP_404_NOT_FOUND
        )
    
    # 获取新密码（支持多种字段名）
    new_password = password_data.get("password") or password_data.get("new_password")
    if not new_password:
        return error_response(
            error_type="VALIDATION_ERROR",
            message="Password is required. Please provide 'password' or 'new_password' field.",
            code=status.HTTP_400_BAD_REQUEST
        )
    
    # 验证密码长度
    if len(new_password) < 6:
        return error_response(
            error_type="VALIDATION_ERROR",
            message="Password must be at least 6 characters long",
            code=status.HTTP_400_BAD_REQUEST
        )
    
    # 生成密码哈希
    hashed_password = get_password_hash(new_password)
    
    # 更新密码
    await db.users.update_one(
        {"_id": object_id},
        {"$set": {"hashed_password": hashed_password}}
    )
    
    return success_response(message="Password reset successfully")

@router.post("/batch-activate", response_model=ApiResponse, summary="批量激活用户")
async def batch_activate_users(
    user_ids: dict,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    批量激活用户
    需要权限：user:update
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_UPDATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    ids = user_ids.get("ids", [])
    if not ids:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="User IDs are required"
        )
    
    # 验证ObjectId格式
    object_ids = []
    for user_id in ids:
        try:
            object_ids.append(ObjectId(user_id))
        except Exception:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Invalid user ID format: {user_id}"
            )
    
    # 批量激活用户
    result = await db.users.update_many(
        {"_id": {"$in": object_ids}},
        {"$set": {"status": UserStatus.ACTIVE}}
    )
    
    return success_response(message=f"Successfully activated {result.modified_count} users")

@router.post("/batch-deactivate", response_model=ApiResponse, summary="批量停用用户")
async def batch_deactivate_users(
    user_ids: dict,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    批量停用用户
    需要权限：user:update
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_UPDATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    ids = user_ids.get("ids", [])
    if not ids:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="User IDs are required"
        )
    
    # 验证ObjectId格式
    object_ids = []
    for user_id in ids:
        try:
            object_ids.append(ObjectId(user_id))
        except Exception:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Invalid user ID format: {user_id}"
            )
    
    # 不能停用自己
    current_user_id = current_user.get("user_id")
    if current_user_id in ids:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Cannot deactivate yourself"
        )
    
    # 批量停用用户
    result = await db.users.update_many(
        {"_id": {"$in": object_ids}},
        {"$set": {"status": UserStatus.INACTIVE}}
    )
    
    return success_response(message=f"Successfully deactivated {result.modified_count} users")

@router.post("/batch-delete", response_model=ApiResponse, summary="批量删除用户（软删除）")
async def batch_delete_users(
    user_ids: dict,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
) -> Any:
    """
    批量删除用户（软删除）
    需要权限：user:delete
    """
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_DELETE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    ids = user_ids.get("ids", [])
    if not ids:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="User IDs are required"
        )
    
    # 验证ObjectId格式
    object_ids = []
    for user_id in ids:
        try:
            object_ids.append(ObjectId(user_id))
        except Exception:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Invalid user ID format: {user_id}"
            )
    
    # 不能删除自己
    current_user_id = current_user.get("user_id")
    if current_user_id in ids:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Cannot delete yourself"
        )
    
    # 批量软删除用户
    result = await db.users.update_many(
        {"_id": {"$in": object_ids}},
        {"$set": {"status": UserStatus.DELETED}}
    )
    
    return success_response(message=f"Successfully deleted {result.modified_count} users")

@router.patch("/{user_id}/status", response_model=ApiResponse, summary="切换用户状态")
async def toggle_user_status(
    user_id: str,
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
):
    """切换用户状态（激活/禁用）"""
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_UPDATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 验证用户ID格式
    try:
        object_id = ObjectId(user_id)
    except Exception:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Invalid user ID format"
        )
    
    # 不能操作自己
    if current_user.get("user_id") == user_id:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Cannot modify your own status"
        )
    
    # 查找用户
    user = await db.users.find_one({"_id": object_id, "status": {"$ne": UserStatus.DELETED}})
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # 切换状态
    new_status = UserStatus.INACTIVE if user.get("status") == UserStatus.ACTIVE else UserStatus.ACTIVE
    
    # 更新用户状态
    from datetime import datetime
    result = await db.users.update_one(
        {"_id": object_id},
        {
            "$set": {
                "status": new_status,
                "updated_at": datetime.utcnow()
            }
        }
    )
    
    if result.modified_count == 0:
        return error_response(
            error_type="operation_failed",
            message="Failed to update user status",
            code=status.HTTP_400_BAD_REQUEST
        )
    
    return success_response(
        message=f"User {'activated' if new_status == UserStatus.ACTIVE else 'deactivated'} successfully",
        data={"status": new_status}
    )

@router.get("/export", summary="导出用户为 Excel")
async def export_users(
    username: Optional[str] = Query(None, description="用户名筛选"),
    email: Optional[str] = Query(None, description="邮箱筛选"),
    role: Optional[str] = Query(None, description="角色筛选"),
    status: Optional[str] = Query(None, description="状态筛选"),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
):
    """导出用户数据为Excel文件"""
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_READ, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 构建查询条件
    query = {"status": {"$ne": UserStatus.DELETED}}
    
    if username:
        query["name"] = {"$regex": username, "$options": "i"}
    if email:
        query["email"] = {"$regex": email, "$options": "i"}
    if role:
        query["roles"] = {"$in": [role]}
    if status:
        if status == "active":
            query["status"] = UserStatus.ACTIVE
        elif status == "inactive":
            query["status"] = UserStatus.INACTIVE
    
    # 查询用户数据
    cursor = db.users.find(query, {"hashed_password": 0}).sort("created_at", DESCENDING)
    users = await cursor.to_list(length=None)
    
    # 创建Excel工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "用户列表"
    
    # 设置表头
    headers = ["ID", "用户名", "邮箱", "姓名", "手机号", "角色", "状态", "部门", "职位", "创建时间", "更新时间"]
    ws.append(headers)
    
    # 设置表头样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")
    
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
    
    # 填充数据
    for user in users:
        row_data = [
            str(user.get("_id", "")),
            user.get("username", user.get("name", "")),  # 用户名，如果没有username则使用name
            user.get("email", ""),
            user.get("name", ""),  # 姓名
            user.get("phone", ""),
            ", ".join(user.get("roles", [])),
            user.get("status", ""),
            user.get("department", ""),
            user.get("position", ""),
            user.get("created_at", "").strftime("%Y-%m-%d %H:%M:%S") if user.get("created_at") else "",
            user.get("updated_at", "").strftime("%Y-%m-%d %H:%M:%S") if user.get("updated_at") else ""
        ]
        ws.append(row_data)
    
    # 调整列宽
    column_widths = [25, 15, 25, 15, 15, 20, 10, 15, 15, 20, 20]
    for col_num, width in enumerate(column_widths, 1):
        ws.column_dimensions[ws.cell(row=1, column=col_num).column_letter].width = width
    
    # 保存到内存
    output = io.BytesIO()
    wb.save(output)
    output.seek(0)
    
    # 生成文件名
    from datetime import datetime
    filename = f"users_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    
    # 返回文件流
    return StreamingResponse(
        io.BytesIO(output.read()),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={filename}"}
    )

@router.post("/import", summary="导入用户")
async def import_users(
    file: UploadFile = File(...),
    current_user: dict = Depends(get_current_user),
    db: AsyncIOMotorDatabase = Depends(get_db)
):
    """从Excel文件导入用户数据"""
    from datetime import datetime
    import re
    
    # 检查权限
    if not any(perm in current_user.get("permissions", []) for perm in [Permissions.USER_CREATE, Permissions.ADMIN]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Not enough permissions"
        )
    
    # 验证文件类型
    if not file.filename or not file.filename.endswith(('.xlsx', '.xls')):
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="只支持 Excel (.xlsx, .xls) 文件"
        )
    
    try:
        # 读取文件内容
        content = await file.read()
        
        # 使用openpyxl读取Excel文件
        try:
            wb = load_workbook(io.BytesIO(content), data_only=True)
            ws = wb.active
        except Exception as e:
            return error_response(
                error_type="file_read_error",
                message=f"无法读取Excel文件: {str(e)}",
                code=status.HTTP_400_BAD_REQUEST
            )
        
        # 读取表头
        if ws.max_row < 2:
            return error_response(
                error_type="validation_error",
                message="Excel文件至少需要包含表头和数据行",
                code=status.HTTP_400_BAD_REQUEST
            )
        
        headers = []
        for cell in ws[1]:
            headers.append(cell.value if cell.value else "")
        
        # 验证必需的列
        required_columns = ['用户名', '邮箱', '姓名']
        header_map = {header: idx for idx, header in enumerate(headers) if header}
        missing_columns = [col for col in required_columns if col not in header_map]
        
        if missing_columns:
            return error_response(
                error_type="validation_error",
                message=f"缺少必需的列: {', '.join(missing_columns)}。请确保Excel文件包含以下列：用户名、邮箱、姓名",
                code=status.HTTP_400_BAD_REQUEST
            )
        
        # 处理数据
        success_count = 0
        error_count = 0
        errors = []
        
        # 从第2行开始读取数据（第1行是表头）
        for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=False), start=2):
            try:
                # 获取单元格值
                def get_cell_value(col_name: str, default: str = ""):
                    if col_name in header_map:
                        cell = row[header_map[col_name]]
                        value = cell.value if cell.value is not None else ""
                        return str(value).strip() if value else default
                    return default
                
                # 验证必需字段
                username = get_cell_value('用户名')
                name = get_cell_value('姓名')
                email = get_cell_value('邮箱')
                
                if not username and not name:
                    errors.append(f"第{row_idx}行: 用户名或姓名不能同时为空")
                    error_count += 1
                    continue
                
                if not email:
                    errors.append(f"第{row_idx}行: 邮箱不能为空")
                    error_count += 1
                    continue
                
                # 验证邮箱格式
                email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
                if not re.match(email_pattern, email):
                    errors.append(f"第{row_idx}行: 邮箱格式不正确 ({email})")
                    error_count += 1
                    continue
                
                # 如果没有用户名，使用姓名作为用户名
                if not username:
                    username = name
                
                # 检查邮箱是否已存在
                existing_user = await db.users.find_one({
                    "email": email,
                    "status": {"$ne": UserStatus.DELETED}
                })
                
                if existing_user:
                    errors.append(f"第{row_idx}行: 邮箱已存在 ({email})")
                    error_count += 1
                    continue
                
                # 检查用户名是否已存在
                existing_username = await db.users.find_one({
                    "$or": [
                        {"username": username},
                        {"name": username}
                    ],
                    "status": {"$ne": UserStatus.DELETED}
                })
                
                if existing_username:
                    errors.append(f"第{row_idx}行: 用户名已存在 ({username})")
                    error_count += 1
                    continue
                
                # 准备用户数据
                phone = get_cell_value('手机号')
                department = get_cell_value('部门')
                position = get_cell_value('职位')
                roles_str = get_cell_value('角色')
                status_str = get_cell_value('状态', 'active')
                
                # 处理角色
                roles = [UserRole.USER]  # 默认角色
                if roles_str:
                    role_list = [role.strip() for role in roles_str.split(',')]
                    valid_roles = []
                    for role in role_list:
                        # 尝试匹配角色
                        for user_role in UserRole:
                            if user_role.value == role or user_role.value.lower() == role.lower():
                                valid_roles.append(user_role.value)
                                break
                    if valid_roles:
                        roles = valid_roles
                
                # 处理状态
                user_status = UserStatus.ACTIVE
                if status_str:
                    status_lower = status_str.lower()
                    if status_lower in ['inactive', '禁用', 'disabled']:
                        user_status = UserStatus.INACTIVE
                    elif status_lower in ['active', '活跃', '启用']:
                        user_status = UserStatus.ACTIVE
                
                # 计算权限
                permissions = []
                for role in roles:
                    role_permissions = get_permissions_for_role(role)
                    permissions.extend(p for p in role_permissions if p not in permissions)
                
                user_data = {
                    "username": username,
                    "name": name,
                    "email": email,
                    "phone": phone if phone else None,
                    "hashed_password": get_password_hash("123456"),  # 默认密码
                    "roles": roles,
                    "permissions": permissions,
                    "status": user_status,
                    "department": department if department else None,
                    "position": position if position else None,
                    "created_at": datetime.utcnow(),
                    "updated_at": datetime.utcnow()
                }
                
                # 插入用户
                await db.users.insert_one(user_data)
                success_count += 1
                
            except Exception as e:
                errors.append(f"第{row_idx}行: {str(e)}")
                error_count += 1
                continue
        
        return success_response(
            message=f"导入完成，成功: {success_count}，失败: {error_count}",
            data={
                "success_count": success_count,
                "error_count": error_count,
                "errors": errors[:20]  # 只返回前20个错误
            }
        )
        
    except HTTPException:
        raise
    except Exception as e:
        return error_response(
            error_type="operation_failed",
            message=f"文件处理失败: {str(e)}",
            code=status.HTTP_400_BAD_REQUEST
        )